using System;
using System.Data.OleDb;
using System.Data;

namespace QLNS
{
    class Services : DataTable
    {
        private static OleDbConnection m_Connection;

        //public static string m_ConnectionString = "Provider=SQLOLEDB.1;Data Source=DUYCUONG;Integrated Security=SSPI;Initial Catalog=QLNS;";
        //public static string m_ConnectionString = "Provider=SQLOLEDB.1;Data Source=PHE04;Integrated Security=SSPI;Initial Catalog=QLNHANSU";
        
        
        //public static string m_ConnectionString = "Provider=SQLOLEDB.1;Data Source=PHE04;Integrated Security=SSPI;Initial Catalog=QUANLYNHANSU";
        public static string m_ConnectionString = "Provider=SQLOLEDB.1;Data Source=CIA-437FD5AD4F9;Integrated Security=SSPI;Initial Catalog=QLNS";
        //public static string m_ConnectionString = "Provider=SQLOLEDB.1;Data Source=6TH1-E7B2B85BC8;Integrated Security=SSPI;Initial Catalog=QLNS;";
        private OleDbCommand m_Command;
        private OleDbDataAdapter m_Adapter;

        public Services() { }

        public OleDbCommand Command
        {
            get { return m_Command;}
            set { m_Command = value;}
        }
        public void Load(OleDbCommand command)
        {
            m_Command = command;
            try {
                m_Command.Connection = m_Connection;
                m_Adapter = new OleDbDataAdapter();
                m_Adapter.SelectCommand = m_Command;
                this.Clear();
                m_Adapter.Fill(this);
            }catch(Exception e){
                string str = e.Message;
            }
        }
        public static bool OpenConnection()
        {
            try {
                if (m_Connection == null)
                {
                    m_Connection = new OleDbConnection(m_ConnectionString);
                }
                if (m_Connection.State == ConnectionState.Closed)
                {
                    m_Connection.Open();
                }
                return true;
            }
            catch (Exception e) {
                m_Connection.Close();
                return false;
            }
        }
        public void CloseConnection()
        {
            m_Connection.Close();
        }
        public int ExecuteNoneQuery() 
        {
            int result = 0;

            OleDbTransaction stran = null;

            try {
                stran = m_Connection.BeginTransaction();

                m_Command.Connection = m_Connection;
                m_Command.Transaction = stran;

                m_Adapter = new OleDbDataAdapter();

                m_Adapter.SelectCommand = m_Command;

                OleDbCommandBuilder builder = new OleDbCommandBuilder(m_Adapter);

                result = m_Adapter.Update(this);

                stran.Commit();

            }
            catch (Exception e) 
            {
                if (stran != null) stran.Rollback();
            }

            return result;
        }
        public int ExecuteNoneQuery(OleDbCommand cmd)
        {
            int result = 0;

            OleDbTransaction tran = null;

            try {
                tran = m_Connection.BeginTransaction();
                cmd.Connection = m_Connection;
                cmd.Transaction = tran;
                result = cmd.ExecuteNonQuery();

                this.AcceptChanges();

                tran.Commit();
            }            
            catch(Exception e)
            {
                if (tran != null) tran.Rollback();
                throw;            
            }
            return result;
        }
        public object ExecuteScalar(OleDbCommand cmd)
        {
            object result = null;
            OleDbTransaction tran = null;

            try {
                tran = m_Connection.BeginTransaction();
                cmd.Connection = m_Connection;

                cmd.Transaction = tran;

                result = cmd.ExecuteScalar();

                this.AcceptChanges();

                tran.Commit();
                if (result == DBNull.Value)
                {
                    result = null;
                }
            }
            catch (Exception e) {
                if (tran != null) tran.Rollback();
                throw;
            }
            return result;
        }
    }
}
